<?php
include_once('../../../php/postgre.php');
session_start();
$act =' ';
if(isset($_POST['act']))
	$act = $_POST['act'];
	
if($act=='tmpl_laporan'){
	echo 'ok___';
	$cb = new laporan();
	$cb -> view_data();
}

class laporan {
function view_data(){

$skpd = $_POST['skpd'];
$code = $_GET['code'];
$tahun= $_POST['tahun'];
$kec = $_POST['kec'];

if($skpd){
	$fskpd .= "and s.id_skpd = '$skpd' ";
}
if ($kec){
	$fkec .= "and uk.id_kecamatan = '$kec'";
}
if($tahun){
	$ftahunS .="and us.tahun='$tahun'";
	$ftahunK .="and uk.tahun='$tahun'";
}
$no = 1;
if ($code == 'SKPD01'){
	$data = get_datas("select distinct (p.id_program), p.nama_program, s.nama_skpd from mus_usulan_skpd us, mus_usulankecamatan uk, mus_kegiatan k, mus_program p, mus_skpd s, mus_rinc_usulan_kec rk, mus_rinc_usulan_skpd rs
where k.id_program = p.id_program and ((rs.id_uskpd=us.id_uskpd and us.id_kegiatan=k.id_keg and s.id_skpd=us.id_skpd $ftahunS and rs.status=0) or (rk.id_usulankec=uk.id_usulankec and uk.id_kegiatan=k.id_keg and s.id_skpd=uk.id_skpd $ftahunK and rk.status=0)) $fskpd ");
}else if ($code == 'SKPD02'){
	$data = get_datas("select distinct (p.id_program), p.nama_program, s.nama_skpd from mus_usulan_skpd us, mus_usulankecamatan uk, mus_kegiatan k, mus_program p, mus_skpd s, mus_rinc_usulan_kec rk, mus_rinc_usulan_skpd rs
where k.id_program = p.id_program and ((rs.id_uskpd=us.id_uskpd and us.id_kegiatan=k.id_keg and s.id_skpd=us.id_skpd $ftahunS and rs.status=1) or (rk.id_usulankec=uk.id_usulankec and uk.id_kegiatan=k.id_keg and s.id_skpd=uk.id_skpd $ftahunK and rk.status=1)) $fskpd");
}else if ($code == 'SKPD03'){
	$data = get_datas("select distinct (p.id_program), p.nama_program, s.nama_skpd, ke.nama_kecamatan from mus_usulankecamatan uk, mus_kegiatan k, mus_program p, mus_skpd s, mus_kecamatan ke, mus_rinc_usulan_kec rk where k.id_program=p.id_program and s.id_skpd=uk.id_skpd and rk.id_usulankec=uk.id_usulankec and uk.id_kecamatan=ke.id_kecamatan and uk.id_kegiatan=k.id_keg $ftahunK and rk.status=1 $fskpd $fkec");

} /*else if ($code == 'Bid04'){
	$data = get_datas("SELECT distinct(prog.id_program), prog.nama_program, S.nama_skpd  
FROM mus_usulan_skpd usk, mus_usulankecamatan uskec,mus_kegiatan keg, mus_program prog, mus_subbidangbappeda SB, mus_bidang_bappeda B, mus_skpd S, mus_rinc_usulan_kec ruskec, mus_rinc_usulan_skpd rusk
where  keg.id_program = prog.id_program and SB.id_bdgbapeda=B.id_bdgbapeda and SB.id_subbidangbappeda=S.id_subbidangbappeda $findBid
and ((rusk.id_uskpd=usk.id_uskpd and usk.id_kegiatan = keg.id_keg and S.id_skpd=usk.id_skpd $findThnS and rusk.status=1) 
OR (ruskec.id_usulankec=uskec.id_usulankec and uskec.id_kegiatan = keg.id_keg and S.id_skpd=uskec.id_skpd $findThnK and ruskec.status=1)) 
 ");

}
*/
if(count($data) > 0){
	foreach ($data as $program){
		?>
		
			 <tr style='background-color:#dddddd'><td><?php echo $no; ?></td>
				 <td></td>
				 <td><b><?php echo ucfirst($program['nama_program']); ?></b></td>
				 <td> </td>
				<td> </td>
				<td> </td>
				<td> </td>
				<td></td>
				<td><b><?php echo $program['nama_skpd']?></td>
				<td></td>
				<td></td>
				<td></td>
				<td></td>
				<td></td>
			</tr>
		<?php
		if ($code == 'SKPD01'){
			$kegSkpd= get_datas("select distinct us.id_uskpd, us.id_kegiatan, k.nama_kegiatan, us.indikator_program, us.indikator_keluaran, us.indikator_hasil, us.target_hasil,
(select sum(rrs.apbdprov) from mus_rinc_usulan_skpd rrs where rs.id_uskpd=rrs.id_uskpd and rrs.status=0) as t_apbdprov,
(select sum(rrs.apbdkab) from mus_rinc_usulan_skpd rrs where rs.id_uskpd=rrs.id_uskpd and rrs.status=0) as t_apbdkab,
(select sum(rrs.apbn) from mus_rinc_usulan_skpd rrs where rs.id_uskpd=rrs.id_uskpd and rrs.status=0) as t_apbn
from mus_rinc_usulan_skpd rs, mus_usulan_skpd us, mus_program p, mus_kegiatan k, mus_skpd s
where us.id_uskpd=rs.id_uskpd and k.id_program=p.id_program and us.id_kegiatan=k.id_keg and us.id_skpd=s.id_skpd $fskpd and p.id_program=".$program['id_program']." and rs.status=0 $ftahunS ");
			$kegKec = get_datas("select distinct uk.id_usulankec, uk.id_kegiatan, k.nama_kegiatan, uk.indikator_program, uk.indikator_keluaran,uk.indikator_hasil, uk.target_hasil,
(select sum(rrk.apbdprov) from mus_rinc_usulan_kec rrk where rk.id_usulankec=rrk.id_usulankec and rrk.status=0) as t_apbdprov,
(select sum(rrk.apbdkab) from mus_rinc_usulan_kec rrk where rk.id_usulankec=rrk.id_usulankec and rrk.status=0) as t_apbdkab,
(select sum(rrk.apbn) from mus_rinc_usulan_kec rrk where rk.id_usulankec=rrk.id_usulankec and rrk.status=0) as t_apbn
from mus_rinc_usulan_kec rk, mus_usulankecamatan uk, mus_program p, mus_kegiatan k, mus_skpd s
where uk.id_usulankec=rk.id_usulankec and k.id_program=p.id_program and uk.id_kegiatan=k.id_keg and uk.id_skpd=s.id_skpd $fskpd and p.id_program=".$program['id_program']." and rk.status=0 $ftahunK");
			$sql = get_datas ("select distinct uk.id_usulankec, uk.kegiatan, uk.indikator_program, uk.indikator_keluaran, uk.indikator_hasil, uk.target_hasil,
(select sum(rrk.apbdprov) from mus_rinc_usulan_kec rrk where rk.id_usulankec=rrk.id_usulankec and rrk.status=0) as t_apbdprov,
(select sum(rrk.apbdkab) from mus_rinc_usulan_kec rrk where rk.id_usulankec=rrk.id_usulankec and rrk.status=0) as t_apbdkab,
(select sum(rrk.apbn) from mus_rinc_usulan_kec rrk where rk.id_usulankec=rrk.id_usulankec and rrk.status=0) as t_apbn
from mus_rinc_usulan_kec rk, mus_usulankecamatan uk, mus_skpd s
where uk.id_usulankec=rk.id_usulankec and uk.id_kegiatan=0 and uk.id_skpd=s.id_skpd $fskpd and rk.status=0 $ftahunK ");
		}else if ($code == 'SKPD02'){
			$kegSkpd= get_datas("select distinct us.id_uskpd, us.id_kegiatan, k.nama_kegiatan, us.indikator_program, us.indikator_keluaran, us.indikator_hasil, us.target_hasil,
(select sum(rrs.apbdprov) from mus_rinc_usulan_skpd rrs where rs.id_uskpd=rrs.id_uskpd and rrs.status=1) as t_apbdprov,
(select sum(rrs.apbdkab) from mus_rinc_usulan_skpd rrs where rs.id_uskpd=rrs.id_uskpd and rrs.status=1) as t_apbdkab,
(select sum(rrs.apbn) from mus_rinc_usulan_skpd rrs where rs.id_uskpd=rrs.id_uskpd and rrs.status=1) as t_apbn
from mus_rinc_usulan_skpd rs, mus_usulan_skpd us, mus_program p, mus_kegiatan k, mus_skpd s
where us.id_uskpd=rs.id_uskpd and k.id_program=p.id_program and us.id_kegiatan=k.id_keg and us.id_skpd=s.id_skpd $fskpd and p.id_program=".$program['id_program']." and rs.status=1 $ftahunS");
			$kegKec = get_datas("select distinct uk.id_usulankec, uk.id_kegiatan, k.nama_kegiatan, uk.indikator_program, uk.indikator_keluaran,uk.indikator_hasil, uk.target_hasil,
(select sum(rrk.apbdprov) from mus_rinc_usulan_kec rrk where rk.id_usulankec=rrk.id_usulankec and rrk.status=1) as t_apbdprov,
(select sum(rrk.apbdkab) from mus_rinc_usulan_kec rrk where rk.id_usulankec=rrk.id_usulankec and rrk.status=1) as t_apbdkab,
(select sum(rrk.apbn) from mus_rinc_usulan_kec rrk where rk.id_usulankec=rrk.id_usulankec and rrk.status=1) as t_apbn
from mus_rinc_usulan_kec rk, mus_usulankecamatan uk, mus_program p, mus_kegiatan k, mus_skpd s
where uk.id_usulankec=rk.id_usulankec and k.id_program=p.id_program and uk.id_kegiatan=k.id_keg and uk.id_skpd=s.id_skpd $fskpd and p.id_program=".$program['id_program']." and rk.status=1 $ftahunK");
			$sql = get_datas ("select distinct uk.id_usulankec, uk.kegiatan, uk.indikator_program, uk.indikator_keluaran,uk.indikator_hasil, uk.target_hasil,
(select sum(rrk.apbdprov) from mus_rinc_usulan_kec rrk where rk.id_usulankec=rrk.id_usulankec and rrk.status=1) as t_apbdprov,
(select sum(rrk.apbdkab) from mus_rinc_usulan_kec rrk where rk.id_usulankec=rrk.id_usulankec and rrk.status=1) as t_apbdkab,
(select sum(rrk.apbn) from mus_rinc_usulan_kec rrk where rk.id_usulankec=rrk.id_usulankec and rrk.status=1) as t_apbn
from mus_rinc_usulan_kec rk, mus_usulankecamatan uk, mus_skpd s
where uk.id_usulankec=rk.id_usulankec and uk.id_kegiatan=0 and uk.id_skpd=s.id_skpd $fskpd and rk.status=1 $ftahunK");
		}else if ($code == 'SKPD03'){
			$kegKec = get_datas("select distinct uk.id_usulankec, uk.id_kegiatan, k.nama_kegiatan, uk.indikator_program, uk.indikator_keluaran,uk.indikator_hasil, uk.target_hasil,
(select sum(rrk.apbdprov) from mus_rinc_usulan_kec rrk where rk.id_usulankec=rrk.id_usulankec and rrk.status=1) as t_apbdprov,
(select sum(rrk.apbdkab) from mus_rinc_usulan_kec rrk where rk.id_usulankec=rrk.id_usulankec and rrk.status=1) as t_apbdkab,
(select sum(rrk.apbn) from mus_rinc_usulan_kec rrk where rk.id_usulankec=rrk.id_usulankec and rrk.status=1) as t_apbn
from mus_rinc_usulan_kec rk, mus_usulankecamatan uk, mus_program p, mus_kegiatan k, mus_skpd s, mus_kecamatan ke
where uk.id_usulankec=rk.id_usulankec and k.id_program=p.id_program and uk.id_kegiatan=k.id_keg and uk.id_skpd=s.id_skpd and uk.id_kecamatan=ke.id_kecamatan $fkec $fskpd and p.id_program=".$program['id_program']." and rk.status=1 $ftahunK");
			$sql = get_datas ("select distinct uk.id_usulankec, uk.kegiatan, uk.indikator_program, uk.indikator_keluaran,uk.indikator_hasil, uk.target_hasil,
(select sum(rrk.apbdprov) from mus_rinc_usulan_kec rrk where rk.id_usulankec=rrk.id_usulankec and rrk.status=1) as t_apbdprov,
(select sum(rrk.apbdkab) from mus_rinc_usulan_kec rrk where rk.id_usulankec=rrk.id_usulankec and rrk.status=1) as t_apbdkab,
(select sum(rrk.apbn) from mus_rinc_usulan_kec rrk where rk.id_usulankec=rrk.id_usulankec and rrk.status=1) as t_apbn
from mus_rinc_usulan_kec rk, mus_usulankecamatan uk, mus_skpd s, mus_kecamatan ke
where uk.id_usulankec=rk.id_usulankec and uk.id_kegiatan=0 and uk.id_skpd=s.id_skpd and uk.id_kecamatan=ke.id_kecamatan $fkec $fskpd and rk.status=1 $ftahunK ");
		}/*else if ($code == 'Bid04'){
			$kegSkpd= get_datas("select distinct R.id_uskpd, id_kegiatan, nama_kegiatan, indikator_program, indikator_keluaran,indikator_hasil, target_hasil, 
										(select sum(ur.apbdprov) from mus_rinc_usulan_skpd ur where R.id_uskpd=ur.id_uskpd and ur.status=1)as tot_apbdprov,
										(select sum(ur.apbdkab) from mus_rinc_usulan_skpd ur where R.id_uskpd=ur.id_uskpd and ur.status=1)as tot_apbdkab,
										(select sum(ur.apbn) from mus_rinc_usulan_skpd ur where R.id_uskpd=ur.id_uskpd and ur.status=1)as tot_apbn
										from mus_rinc_usulan_skpd R,mus_usulan_skpd usk, mus_program prog, mus_kegiatan keg  
										where usk.id_uskpd=R.id_uskpd and keg.id_program=prog.id_program and R.status=1 and
										(usk.id_kegiatan=keg.id_keg  )
										and prog.id_program=".$program['id_program']);
			$kegKec = get_datas("select distinct R.id_usulankec, id_kegiatan, nama_kegiatan, indikator_program, indikator_keluaran,indikator_hasil, target_hasil, 
					 (select sum(ur.apbdprov) from mus_rinc_usulan_kec ur where R.id_usulankec=ur.id_usulankec and ur.status=1)as tot_apbdprov,
					 (select sum(ur.apbdkab) from mus_rinc_usulan_kec ur where R.id_usulankec=ur.id_usulankec and ur.status=1)as tot_apbdkab,
					 (select sum(ur.apbn) from mus_rinc_usulan_kec ur where R.id_usulankec=ur.id_usulankec and ur.status=1)as tot_apbn
					 from mus_rinc_usulan_kec R,mus_usulankecamatan uskec, mus_program prog, mus_kegiatan keg
					 where keg.id_program=prog.id_program and  uskec.id_usulankec=R.id_usulankec and R.status=1 and
					 (uskec.id_kegiatan=keg.id_keg  )
					 and  prog.id_program=".$program['id_program']);
			$sql = get_datas ("select distinct uskec.id_usulankec, s.id_skpd, s.nama_skpd, uskec.indikator_hasil, 
				uskec.indikator_program, uskec.indikator_keluaran, uskec.target_hasil, uskec.kegiatan,
				(select sum(ur.apbdprov) from mus_rinc_usulan_kec ur where R.id_usulankec=ur.id_usulankec and ur.status=1)as tot_apbdprov,
				(select sum(ur.apbdkab) from mus_rinc_usulan_kec ur where R.id_usulankec=ur.id_usulankec and ur.status=1)as tot_apbdkab,
				(select sum(ur.apbn) from mus_rinc_usulan_kec ur where R.id_usulankec=ur.id_usulankec and ur.status=1)as tot_apbn
				from mus_usulankecamatan uskec,	mus_rinc_usulan_kec R, mus_skpd s 
				where s.id_skpd=uskec.id_skpd and R.id_usulankec=uskec.id_usulankec and (uskec.id_kegiatan=0 and R.status=1
				$findThnK )order by uskec.id_usulankec");
		}
		*/
		if(count($kegSkpd) > 0){
			foreach ($kegSkpd as $kegSkpd){
				$apbdkabb = $kegSkpd['t_apbdprov'];
				$apbdprovv = $kegSkpd['t_apbdkab'];
				$apbnn = $kegSkpd['t_apbn'];
				$total = $apbdkabb + $apbdprovv + $apbnn;
			?>	
				<tr style='background-color:#00ddff'>
					<td style="text-align:center;">&nbsp;</td>
					<td style="text-align:center;"> </td>
					<td><b><?php echo $kegSkpd['nama_kegiatan']?></b></td>
					<td><b><?php echo $kegSkpd['indikator_program']?></b></td>
					<td><b><?php echo $kegSkpd['indikator_keluaran']?></b> </td>
					<td><b><?php echo $kegSkpd['indikator_hasil']?></b>  </td>
					<td><b><?php echo $kegSkpd['indikator_hasil']?></b>   </td>
					<td><b><?php echo $kegSkpd['target_hasil']?></td>
					<td>  </td>
					<td><b><?php echo $kegSkpd['t_apbdkab']?></td>
					<td><b><?php echo $kegSkpd['t_apbdprov']?></td>
					<td><b><?php echo $kegSkpd['t_apbn']?></td>
					<td><b><?php echo $total ?></b></td>
					<td></td>
				</tr>
			<?php
				$detkegiatansk= get_datas("select * from mus_rinc_usulan_skpd r, mus_usulan_skpd usk 
								where usk.id_uskpd=r.id_uskpd and r.id_uskpd=".$kegSkpd['id_uskpd']." and r.status=1 order by r.id_rinc_usulan_skpd");
					foreach ($detkegiatansk as $detsk){
						$apbdkabdet = $detsk['apbdprov'];
						$apbdprovdet = $detsk['apbdkab'];
						$apbndet = $detsk['apbn'];
						$totalsk = $apbdkabdet + $apbdprovdet + $apbndet;
										
					?>	<tr style='background-color:#ffffff'>
							<td style="text-align:center;"></td>
							<td style="text-align:center;"> </td>
							<td> </td>
							<td> </td>
							<td><?php echo $detsk['usulan']?> </td>
							<td><?php echo $detsk['prioritas']?>  </td>
							<td></td>
							<td> </td>
							<td>  </td>
							<td><?php echo $detsk['apbdkab']?></td>
							<td><?php echo $detsk['apbdprov']?></td>
							<td><?php echo $detsk['apbn']?></td>
							<td><?php echo $totalsk ?></td>
							<td><?php echo $detsk['lokasi']?></td>
						</tr>
					<?
				}
			}
		}
		if(count ($kegKec) > 0){
			foreach ($kegKec as $kegKec){
				$apbdkabb = $kegKec['tot_apbdprov']; 
				$apbdprovv = $kegKec['tot_apbdkab'];
				$apbnn = $kegKec['tot_apbn'];
				$totall = $apbdkabb + $apbdprovv + $apbnn;
			?>
				<tr style='background-color:#00ddff'>
					 <td style="text-align:center;"></td>
					 <td style="text-align:center;"> </td>
					 <td><b><?php echo $kegKec['nama_kegiatan']?></b></td>
					 <td><b><?php echo $kegKec['indikator_program']?></b></td>
					 <td><b><?php echo $kegKec['indikator_keluaran']?></b> </td>
					 <td><b><?php echo $kegKec['indikator_hasil']?></b>  </td>
					 <td><b><?php echo $kegKec['indikator_hasil']?></b>   </td>
					 <td><b><?php echo $kegKec['target_hasil']?></td>
					 <td>  </td>
					 <td><b><?php echo $kegKec['t_apbdkab']?></td>
					 <td><b><?php echo $kegKec['t_apbdprov']?></td>
					 <td><b><?php echo $kegKec['t_apbn']?></td>
					 <td><b><?php echo $totall ?></b></td>
					 <td></td>
				 </tr>
				 <?php
					$detkegiatankec= get_datas("select * from mus_rinc_usulan_kec r, mus_usulankecamatan uskec 
							where uskec.id_usulankec=r.id_usulankec and r.id_usulankec=".$kegKec['id_usulankec']." and r.status=1 order by r.id_rinc_usulan_kec");
						foreach ($detkegiatankec as $detkec){
							$apbdkabdet = $detkec['apbdprov'];
							$apbdprovdet = $detkec['apbdkab'];
							$apbndet = $detkec['apbn'];
							$totaldet = $apbdkabdet + $apbdprovdet + $apbndet;
											
						?>	<tr style='background-color:#ffffff'>
								<td style="text-align:center;"></td>
								<td style="text-align:center;"> </td>
								<td> </td>
								<td> </td>
								<td><?php echo $detkec['usulan']?> </td>
								<td> <?php echo $detkec['prioritas']?>  </td>
								<td></td>
								<td> </td>
								<td>  </td>
								<td> <?php echo $detkec['apbdkab']?></td>
								<td> <?php echo $detkec['apbdprov']?></td>
								<td> <?php echo $detkec['apbn']?></td>
								<td> <?php echo $totaldet ?> </td>
								<td><?php echo $detkec['lokasi']?></td>
							</tr>
						<?
					}
			}
		}
		$no ++;

	}
} 
if(count ($sql) > 0){
foreach ($sql as $sql){
	$apbdkabdet = $sql['t_apbdprov'];
	$apbdprovdet = $sql['t_apbdkab'];
	$apbndet = $sql['t_apbn'];
	$totalsql = $apbdkabdet + $apbdprovdet + $apbndet;

?>
	<tr style='background-color:#00ddff'><td><?php echo $no; ?></td>
	   <td></td>
	   <td><b><?php echo ucfirst($sql['kegiatan']); ?></b></td>
	   <td> </td>
			<td> </td>
			<td> </td>
			<td> </td>
			<td></td>
			<td><b><?php echo $sql['nama_skpd']?></td>
			<td><b><?php echo $sql['t_apbdkab']?></td>
			 <td><b><?php echo $sql['t_apbdprov']?></td>
			 <td><b><?php echo $sql['t_apbn']?></td>
			 <td><b><?php echo $totalsql ?></b></td>
			<td></td>
	</tr>
	<?php
	$sqldet = get_datas("select * from mus_rinc_usulan_kec r, mus_usulankecamatan uskec 
		where uskec.id_usulankec=r.id_usulankec and r.id_usulankec=".$sql['id_usulankec']." 
		and r.status=1 order by r.id_rinc_usulan_kec");
		foreach ($sqldet as $detkectk){
							$apbdkabdet = $detkectk['apbdprov'];
							$apbdprovdet = $detkectk['apbdkab'];
							$apbndet = $detkectk['apbn'];
							$totaltk = $apbdkabdet + $apbdprovdet + $apbndet;
											
						?>	<tr style='background-color:#ffffff'>
								<td style="text-align:center;"></td>
								<td style="text-align:center;"> </td>
								<td> </td>
								<td> </td>
								<td><?php echo $detkectk['usulan']?> </td>
								<td> <?php echo $detkectk['prioritas']?></b>  </td>
								<td></td>
								<td> </td>
								<td>  </td>
								<td> <?php echo $detkectk['apbdkab']?></td>
								<td> <?php echo $detkectk['apbdprov']?></td>
								<td> <?php echo $detkectk['apbn']?></td>
								<td> <?php echo $totaltk ?></td>
								<td><?php echo $detkectk['lokasi']?></td>
							</tr>
						<?
					}
	$no++;
	}
	}
}
}
?>